﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace FirmaTransportowa
{
    public class Transport : IModel<Transport>
    {
        [System.ComponentModel.DisplayName("ID transportu")]
        public int id { get; set; }
        [System.ComponentModel.DisplayName("ID pojazdu")]
        public Vehicle vehicle { get; set; }
        [System.ComponentModel.DisplayName("ID pracownika")]
        public Worker dispatcher { get; set; }
        [System.ComponentModel.DisplayName("ID kierowcy")]
        public Worker driver { get; set; }
        [System.ComponentModel.DisplayName("Status transportu")]
        public TransportStatus status { get; set; }
        [System.ComponentModel.DisplayName("Data transportu")]
        public DateTime transport_date { get; set; }
        [System.ComponentModel.DisplayName("Czas rozpoczęcia")]
        public DateTime transport_start_time { get; set; }
        [System.ComponentModel.DisplayName("Czas zakończenia")]
        public DateTime transport_end_time { get; set; }
        [System.ComponentModel.DisplayName("Miasto startowe")]
        public String start_city { get; set; }
        [System.ComponentModel.DisplayName("Miasto docelowe")]
        public String destination_city { get; set; }
        [System.ComponentModel.DisplayName("Przebyta odległość")]
        public int distance { get; set; }

        public Transport(int id, int vehicle_id, int worker_id, int driver_id, string status, DateTime transport_date, DateTime transport_start_time, 
            DateTime transport_end_time, string start_city, string destination_city, int distance)
        {
            this.id = id;
            this.vehicle = Vehicle.getById(vehicle_id);
            this.dispatcher = Worker.getById(worker_id);
            this.driver = Worker.getById(driver_id);
            this.status = EnumUtils.getTransportStatusByDescription(status);
            this.transport_date = transport_date;
            this.transport_start_time = transport_start_time;
            this.transport_end_time = transport_end_time;
            this.start_city = start_city;
            this.destination_city = destination_city;
            this.distance = distance;

        }

        public Transport()
        {

        }

        public int ReturnValue
        {
            get
            {
                return this.id;
            }
        }

        public int save()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            int createdId = -1;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                bool newObject = false;

                if (Transport.getById(this.id) == null)
                {
                    cmd.CommandText = "INSERT INTO Transport(vehicle_ID, worker_ID, driver_ID, status, transport_date, transport_start_time, transport_end_time, start_city, destination_city, distance) VALUES(@vehicle_id, @worker_id, @driver_id, @status, @transport_date, @transport_start_time, @transport_end_time, @start_city, @destination_city, @distance) SELECT CONVERT(int, SCOPE_IDENTITY())";
                    newObject = true;
                }
                else
                {
                    cmd.CommandText = "UPDATE Transport SET vehicle_ID=@vehicle_id, worker_ID=@worker_id, driver_ID=@driver_id, status=@status, transport_date=@transport_date, transport_start_time=@transport_start_time, transport_end_time=@transport_end_time, start_city=@start_city, destination_city=@destination_city, distance=@distance WHERE transport_ID=@id";
                    cmd.Parameters.AddWithValue("@id", this.id);
                }

                cmd.Parameters.AddWithValue("@vehicle_id", this.vehicle.id);
                cmd.Parameters.AddWithValue("@worker_id", this.dispatcher.id);
                cmd.Parameters.AddWithValue("@driver_id", this.driver.id);
                cmd.Parameters.AddWithValue("@status", EnumUtils.getEnumDescription(this.status));
                cmd.Parameters.AddWithValue("@transport_date", this.transport_date);
                cmd.Parameters.AddWithValue("@transport_start_time", this.transport_start_time);
                cmd.Parameters.AddWithValue("@transport_end_time", this.transport_end_time);
                cmd.Parameters.AddWithValue("@start_city", this.start_city);
                cmd.Parameters.AddWithValue("@destination_city", this.destination_city);
                cmd.Parameters.AddWithValue("@distance", this.distance);
                cmd.Connection = conn;

                if (newObject)
                {
                    createdId = (int)cmd.ExecuteScalar();
                }
                else
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return createdId;
        }
        public static List<Transport> list()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            List<Transport> list = new List<Transport>();

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM Transport", conn);
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Transport transport = Transport.getById(int.Parse(reader["transport_ID"].ToString()));

                        list.Add(transport);
                    }
                }
                reader.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return list;
        }

        public static BindingSource getTransportsListForDispatcher(int userId) {
            SqlConnection connection = SqlConnectionFactory.getConnection();
            BindingSource bindingSource = new BindingSource();

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand("SELECT t.transport_ID, t.status, t.transport_date, " +
                                        "t.transport_start_time, t.transport_end_time, t.start_city, " +
                                        "t.destination_city, t.distance, v.brand, v.model, v.capacity, d.name, d.surname " +
                                        "FROM (((Transport t JOIN Vehicle v ON t.vehicle_ID = v.vehicle_ID) JOIN Worker d ON d.worker_ID = t.driver_ID) " +
                                        "JOIN Worker w ON w.worker_ID = t.worker_ID) WHERE w.worker_ID = (SELECT u.worker_id FROM User_ u WHERE u.user_ID = " + userId + ") ORDER BY t.transport_date DESC", connection);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                bindingSource.DataSource = dataTable;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                connection.Close();
            }

            return bindingSource;
        }


        public static BindingSource getTransportsListForDriver(int userId)
        {
            SqlConnection connection = SqlConnectionFactory.getConnection();
            BindingSource bindingSource = new BindingSource();
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand("SELECT t.transport_ID, t.status, t.transport_date, " +
                                        "t.transport_start_time, t.transport_end_time, t.start_city, " +
                                        "t.destination_city, t.distance, v.brand, v.model, v.capacity, w.name, w.surname " +
                                        "FROM (((Transport t JOIN Vehicle v ON t.vehicle_ID = v.vehicle_ID) JOIN Worker w ON w.worker_ID = t.worker_ID) " +
                                        "JOIN Worker d ON d.worker_ID = t.driver_ID) WHERE d.worker_ID = (SELECT u.worker_id FROM User_ u WHERE u.user_ID = " + userId + ") ORDER BY t.transport_date DESC", connection);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                bindingSource.DataSource = dataTable;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                connection.Close();
            }

            return bindingSource;
        }

        public static BindingSource getTransportsListForDriver(int id, TransportStatus status)
        {
            SqlConnection connection = SqlConnectionFactory.getConnection();
            BindingSource bindingSource = new BindingSource();

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand("SELECT t.transport_ID, t.status, t.transport_date, " +
                                    "t.transport_start_time, t.transport_end_time, t.start_city, " +
                                    "t.destination_city, t.distance, v.brand, v.model, v.capacity, w.name, w.surname, d.name, d.surname " +
                                    "FROM (((Transport t JOIN Vehicle v ON t.vehicle_ID = v.vehicle_ID) JOIN Worker w ON w.worker_ID = t.worker_ID) " +
                                    "JOIN Worker d ON d.worker_ID = t.driver_ID) WHERE d.worker_ID = (SELECT u.worker_id FROM User_ u WHERE u.user_ID = " + id + ") AND t.status = '" + EnumUtils.getEnumDescription(status) + "' ORDER BY t.transport_date DESC", connection);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                bindingSource.DataSource = dataTable;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                connection.Close();
            }

            return bindingSource;
        }

        public static Transport getById(int id)
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                if (id < 0)
                {
                    throw new ArgumentException();
                }

                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "SELECT * FROM Transport WHERE transport_ID=@id;";
                cmd.Parameters.AddWithValue("@id", id);
                cmd.Connection = conn;
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow);

                if (reader.HasRows)
                {
                    reader.Read();
                    Transport transport = new Transport();
                    transport.id = int.Parse(reader["transport_ID"].ToString());
                    transport.vehicle = Vehicle.getById(int.Parse(reader["vehicle_ID"].ToString()));
                    transport.dispatcher = Worker.getById(int.Parse(reader["worker_ID"].ToString()));
                    transport.driver = Worker.getById(int.Parse(reader["driver_ID"].ToString()));
                    transport.status = EnumUtils.getTransportStatusByDescription(reader["status"].ToString());
                    transport.transport_date = DateTime.Parse(reader["transport_date"].ToString());
                    transport.transport_start_time = DateTime.Parse(reader["transport_start_time"].ToString());
                    transport.transport_end_time = DateTime.Parse(reader["transport_end_time"].ToString());
                    transport.start_city = reader["start_city"].ToString();
                    transport.destination_city = reader["destination_city"].ToString();
                    transport.distance = int.Parse(reader["distance"].ToString());

                    conn.Close();
                    return transport;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();

            }

            return null;

        }

        public static int getDriverKm(String name, String surname, DateTime startTime, DateTime endTime)
        {
            int km;

            SqlConnection conn = SqlConnectionFactory.getConnection();
            km = 0;

            try
            {
                int id = Worker.getIdByNameAndSurname(name,surname);

                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SELECT distance FROM Transport WHERE (transport_date BETWEEN '" + startTime + "' AND '" + endTime + "') AND driver_ID = '" + id + "' AND status = '" + EnumUtils.getEnumDescription(TransportStatus.CLOSED) + "'";

                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        km += int.Parse(reader["distance"].ToString());
                    }
                }

                reader.Close();
                cmd.ExecuteReader();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();

            }            

            return km;
        }

        public static BindingSource getDriversStatistics()
        {
            SqlConnection connection = SqlConnectionFactory.getConnection();
            BindingSource bindingSource = new BindingSource();

            try
            {
                connection.Open();

                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                //command.CommandText = "SELECT w.name, w.surname FROM (Worker w JOIN Transport t ON w.worker_ID = t.driver_ID) WHERE w.occupation_ID=1;";
                command.CommandText = "SELECT name, surname, COUNT(worker_ID) FROM Worker";
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                bindingSource.DataSource = dataTable;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                connection.Close();
            }

            return bindingSource;
        }

        public void delete()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "DELETE FROM Transport WHERE transport_ID=@id";
                cmd.Parameters.AddWithValue("@id", this.id);

                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }
        }

        public void startTransport() {
            //aktualizacja statusu, daty i czasu rozpoczęcia transportu
            this.status = TransportStatus.IN_PROGRESS;
            this.transport_date = DateTime.ParseExact(DateTime.Now.ToString("ddMMyyyy"), "ddMMyyyy", null);
            this.transport_start_time = DateTime.ParseExact(DateTime.Now.ToString("HH:mm"), "HH:mm", null);
            this.save();

            //jeżeli transport rozpoczyna się w sortowni aktualizacja statusów paczek
            if (this.destination_city != "sortownia") 
            {
                List<Package> packages = Package.getPackagesByTransport(this);

                foreach (Package package in packages) 
                {
                    package.status = PackageStatus.DELIVERED;
                    package.save();
                }
            }
        }

        public void finishTransport() {
            //aktualizacja statusu i czasu zakończenia transportu
            this.status = TransportStatus.CLOSED;
            this.transport_end_time = DateTime.ParseExact(DateTime.Now.ToString("HH:mm"), "HH:mm", null);
            this.save();
        }
    }
}
